﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spRest_GetDbFileNames]
#-- Purpose:		Gets the names of the data files as they should be for the
#--					restore
#--	Last Update:	12/09/2015
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spRest_GetDbFileNames]
(
	@database_name			sysname,
	@backup_set_id			int
)
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE	@data_dir nvarchar(3000), @log_dir nvarchar(3000), @templateFiles varchar(MAX), @cmd varchar(MAX)

--- Get the default data and log directories
EXEC	[dbo].[spMaint_GetDefaultDataDirectory]		@data_directory_name = @data_dir OUTPUT
EXEC	[dbo].[spMaint_GetDefaultLogDirectory]		@log_directory_name = @log_dir OUTPUT

--- Get database file names
SET		@templateFiles = '
;WITH model_db AS
(
	SELECT	''D'' file_type, ''[VAR_DATADIR]'' directory_name
	UNION
	SELECT	''L'' file_type, ''[VAR_LOGDIR]'' directory_name
), bkp AS
(
	SELECT		bs.[database_name],
				bf.[logical_name],
				[physical_name] = REPLACE(bf.[physical_name], bf.[physical_drive], UPPER(bf.[physical_drive])),
				[physical_name_should_be] =
					(CASE	WHEN pdt.[physical_name] IS NOT NULL THEN ISNULL(pdt.[physical_name], REPLACE(bf.[physical_name], bf.[physical_drive], UPPER(bf.[physical_drive]))) 
							WHEN pdt.[physical_name] IS NULL AND bs.[database_name] = ''[VAR_DBNAME]'' AND [dbo].[fnIO_FileExists](bf.[physical_name]) = 1 THEN REPLACE(bf.[physical_name], bf.[physical_drive], UPPER(bf.[physical_drive]))
							ELSE /* bs.[database_name] <> @database_name */ f.[directory_name] + ''[VAR_DBNAME]'' + ''_file'' + CAST(bf.[file_number] as varchar(100)) +
									(CASE	WHEN bf.[file_type] = ''L'' THEN ''.ldf''
											WHEN bf.[file_type] = ''D'' AND bf.[file_number] < 2 THEN ''.mdf''
											ELSE ''.ndf''
											END)
							END),
				f.[directory_name],
				[db_logical_name] =
					(CASE	WHEN pdt.[physical_name] IS NOT NULL THEN ISNULL(pdt.[logical_name], bf.[logical_name]) 
							WHEN pdt.[physical_name] IS NULL AND bs.[database_name] = ''[VAR_DBNAME]'' THEN bf.[logical_name]
							ELSE /* bs.[database_name] <> @database_name */ ''[VAR_DBNAME]'' +  
									''_file_'' + CAST(bf.[file_number] as varchar(100))
							END)
	FROM		[msdb].[dbo].[backupfile] bf 
	JOIN		[msdb].[dbo].[backupset] bs ON bf.[backup_set_id] = bs.[backup_set_id]
	LEFT JOIN	[dbo].[tblRest_FileNames] pdt ON bf.[file_type] = pdt.[file_type]
				AND ISNULL(bf.[filegroup_name], '''') = ISNULL(pdt.[filegroup_name], '''')
				AND bf.[file_number] = pdt.[file_number]
				AND pdt.[database_name] <> bs.[database_name]
				AND pdt.[database_name] = ''[VAR_DBNAME]''
	JOIN		model_db f ON bf.[file_type] = f.[file_type]
	WHERE		bf.[backup_set_id] = [VAR_BACKUPSETID]
)
SELECT		logical_name,
			db_logical_name,
			physical_name,
			physical_name_should_be,
			(CASE WHEN logical_name = db_logical_name THEN 0 ELSE 1 END) rename_logical_name,
			(CASE WHEN physical_name = physical_name_should_be THEN 0 ELSE 1 END) rename_physical_name
FROM		bkp
	'

--- Return the resultset
SET		@cmd =	REPLACE(
					REPLACE(
						REPLACE(
							REPLACE(@templateFiles, '[VAR_DBNAME]', @database_name)
						, '[VAR_BACKUPSETID]', CAST(@backup_set_id as varchar(100)))
					, '[VAR_DATADIR]', @data_dir)
				, '[VAR_LOGDIR]', @log_dir)
EXEC (@cmd)

SET NOCOUNT OFF
